import xlrd
import pprint


def read_excel():
    # 打开文件
    workbook = xlrd.open_workbook(r'12月份衣服销售数据.xlsx')
    sheet = workbook.sheet_by_name('12月份各种服饰销售情况')
    # 获取整行或者整列的值
    cols1 = sheet.col_values(1)  # 第二列值
    cols2 = sheet.col_values(2)  # 第三列值
    cols4 = sheet.col_values(4)  # 第五列值
    cols1 = cols1[1:]   # 切片消除字段
    # print(cols1)
    # print(cols2)
    # print(cols4)
    prices = []
    singleprice = []
    singlenum = []
    totalprice = 0
    totalnum = 0
    dir = {}

    # j = 0
    # k = 0

    # 每件商品的销售额
    for i in range(1, len(cols2)):
        price = cols2[i] * cols4[i]
        prices.append(price)
    # print(prices)

    # 总销售额
    for i in range(0, len(prices)):
        totalprice = totalprice + prices[i]

    # 总销售量
    for i in range(1, len(cols4)):
        totalnum = totalnum + cols4[i]
    # print(totalnum)

    # 每种商品的销售额占比
    for i in range(1, len(cols2)):
        singleprice.append(prices[i - 1]/totalprice)
    # print(singleprice)

    # 验证销售额占比和为1
    # for i in range(0,len(singleprice)):
    #     j = j + singleprice[i]
    # print(j)

    # 每种商品销售量占比
    for i in range(1, len(cols4)):
        singlenum.append(cols4[i]/totalnum)
    # print(singlenum)

    # # 验证销售量占比和为1
    # for i in range(0,len(singlenum)):
    #     k = k + singleprice[i]
    # print(k)

    # 商品去重
    norepeatcols1 = []
    for i in cols1:
        if i not in norepeatcols1:
            norepeatcols1.append(i)
    # print(norepeatcols1)

    # 多表齐运算 ！！！！
    for ele in norepeatcols1:
        lastprice = 0
        lastnum = 0
        for i in range(0, len(cols1)):
            if ele == cols1[i]:
                lastprice += singleprice[i]
                lastnum += singlenum[i]
                # print(i)
                # print(lastprice)
                dir[ele] = [lastprice, lastnum]

    # 输出
    print("------------------vinck商城-------------------")
    print("--类别--------销售额占比------------销售量占比----")
    pprint.pprint(dir, sort_dicts=False)
    print("----------------------------------------------")
    print("--总计金额-------------------------------------")
    print(totalprice)


if __name__ == '__main__':
    read_excel()
